package bosch;

import java.io.File;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.sql.*;
import java.util.Arrays;
import java.util.List;
import java.util.Vector;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;

public class Utils {
	
	public static void createTablesFromCSVFiles(File directory) throws Exception
	{
		Connection connection = null;
		Statement statement = null;
		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection("jdbc:sqlite:Database.db");
		statement = connection.createStatement();
		for(File fileEntry : directory.listFiles())
		{
			if(fileEntry.getName().endsWith(".csv"))
			{
			
				//Reads the file
				List<String> contents = Files.readAllLines(fileEntry.toPath(), StandardCharsets.UTF_8);
					
				//Gets and transforms the column names
				String line = contents.get(0);
				Vector<String> columns = new Vector<String>(Arrays.asList(line.split(",")));
					
				//Checks if the table exists, if not it must be created
				String tableName = fileEntry.getName().toLowerCase().replace(".csv", "");
				String check = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name=" + "'" + tableName + "'";
				ResultSet exists = statement.executeQuery(check);
				exists.next();
				if(exists.getInt(1) == 0)
				{
					//CREATE TABLE statement
					String createTable = "CREATE TABLE " + tableName + "( ";							
					for(int i = 0; i < columns.size(); ++i)
					{
						if(i==0) createTable += columns.get(i) + " REAL";
						else createTable += ", " + columns.get(i) + " REAL";
					}
					createTable += ");";
					statement.executeUpdate(createTable);
					
					//Populates the table
					for(int j = 1; j < contents.size(); ++j)
					{
						String insert = "INSERT INTO " + tableName + " VALUES (" + contents.get(j) + ");";
						statement.executeUpdate(insert);								
					}
				}						
			}
		}
		statement.close();
		connection.close();
	}	
	
	public static ResultSet RunSQLScriptOnDB(File script) throws Exception
	{	
		Connection connection = null;
		Statement statement = null;
		Class.forName("org.sqlite.JDBC");
		connection = DriverManager.getConnection("jdbc:sqlite:Database.db");
		statement = connection.createStatement();
		List<String> contents = Files.readAllLines(script.toPath(), StandardCharsets.UTF_8);		
		String query = GetQuery(contents);
		return statement.executeQuery(query);
	}
	
	private static String GetQuery(List<String> list)
	{
		String result = new String();
		for(String element : list)
		{
			result += " " + element;			
		}
		return result;
	}
	
	public static void DisplayResults(ResultSet results, String title) throws Exception
	{				
		JFrame frame = new JFrame(title);
        frame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        
        ResultSetMetaData resultsMeta = results.getMetaData();
        int columnCount = resultsMeta.getColumnCount();
        
        Vector<Vector<Object>> rowData = new Vector<Vector<Object>>();    
        Vector<String> columns = new Vector<String>();
        for(int i = 1; i <= columnCount; ++i)
        {
        	columns.add(resultsMeta.getColumnName(i));
        }
		while(results.next())
		{
			Vector<Object> currentRow = new Vector<Object>();
			for(int i = 1; i <= columnCount; ++i)
			{
				
				int type = resultsMeta.getColumnType(i);
				switch (type)
				{
					case Types.FLOAT:
					case Types.DOUBLE:
					case Types.REAL:
						currentRow.add(results.getDouble(i));
						break;
						
					case Types.INTEGER:
						currentRow.add(results.getInt(i));
						break;						
					
					case Types.VARCHAR:
						currentRow.add(results.getString(i));
						break;
						
					default:
						currentRow.add(results.getString(i));
						break;
				}
			}
			rowData.add(currentRow);
		}	
		JTable table = new JTable(rowData, columns);
        frame.add(new JScrollPane(table));
        table.setVisible(true);
        frame.pack();
        frame.setBounds(200, 200, frame.getWidth(), frame.getHeight());
        frame.setVisible(true);
	}
	
}
